create or replace view vac_app_shortcuts_4_1 as
with sh as
(
select
 workspace,
 workspace_display_name,
 application_id,
 application_name,
 shortcut_name,
 shortcut_type,
 condition_type,
 condition_type_code,
 condition_expression1,
 condition_expression2,
 error_text,
 build_option,
 ac_utils.hs(shortcut) shortcut_hs,
 dbms_lob.substr(shortcut,4000, 1)        shortcut_sr1,
 dbms_lob.substr(shortcut,4000, 4000+1 )  shortcut_sr2,
 dbms_lob.substr(shortcut,4000, 8000+1 )  shortcut_sr3,
 dbms_lob.substr(shortcut,4000, 12000+1)  shortcut_sr4,
 is_subscribed,
 subscribed_from,
 last_updated_by,
 last_updated_on,
 component_comments,
 shortcut_id
from APEX_APPLICATION_SHORTCUTS
)
select
   workspace,
   workspace_display_name,
   application_id,
   application_name,
   shortcut_name,
   to_clob(shortcut_sr1) ||  to_clob(shortcut_sr2) ||
   to_clob(shortcut_sr3) ||  to_clob(shortcut_sr4) shortcut_sr,
   last_updated_by,
   last_updated_on,
   shortcut_id,
   u_name,
   u_value,
   'APEX_APPLICATION_SHORTCUTS' src_view_name
from
(
  select
   workspace,
   workspace_display_name,
   application_id,
   application_name,
   shortcut_name,
   shortcut_sr1,
   shortcut_sr2,
   shortcut_sr3,
   shortcut_sr4,
   last_updated_by,
   last_updated_on,
   shortcut_id,
   u_name,
   u_value
  from sh
  UNPIVOT INCLUDE NULLS
   ( u_value
     FOR    u_name
     IN
     (
       component_comments,
       subscribed_from,
       is_subscribed,
       shortcut_type,
       condition_type,
       condition_type_code,
       condition_expression1,
       condition_expression2,
       error_text,
       build_option,
       shortcut_hs
     )
   )
 ) t;

